Imports System.Data.Common
Partial Class Presentation_Management_CallAverage
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim Sql As String
        Dim Records As DbDataReader
        Dim Params(0) As GenParameter
        Dim CountSql As String

        Page.Title = "View the Number of Calls"

        If Not Page.IsPostBack Then

            'Populate the Executives List
            dlExecutives.DataTextField = "ExecutiveName"
            dlExecutives.DataValueField = "ExecutiveID"

            If Not User.IsInRole("Top Management") Then
                Sql = "SELECT ExecutiveID, ExecutiveName FROM ExecutiveMaster WHERE Location = ?Location" & _
                        " AND Active = 1 AND DepartmentID = 2 ORDER BY ExecutiveName"
            Else
                Sql = "SELECT ExecutiveID, ExecutiveName FROM ExecutiveMaster WHERE" & _
                        " Active = 1 AND DepartmentID = 2 ORDER BY ExecutiveName"
            End If

            

            Params(0) = New GenParameter("?Location", Request.Cookies("Profile").Item("Location"), Data.DbType.String)

            Records = GenHelper.ExecuteReader(Sql, Params, ConfigurationManager.ConnectionStrings.Item("MySqlConnString").ToString())
            dlExecutives.DataSource = Records
            dlExecutives.DataBind()
            Records.Close()

        End If

        ReDim Params(1)

        'Fill the custom pager's Properties

        Sql = "Select ExecutiveMaster.ExecutiveName, YEAR (WorkflowMaster.MeetingDate)AS 'Year', MONTHNAME(WorkflowMaster.MeetingDate) AS 'Month', TRUNCATE(COUNT(*)/IF(YEAR(CURDATE()) = YEAR(WorkflowMaster.MeetingDate) AND MONTH(CURDATE()) = MONTH(WorkflowMaster.MeetingDate),DAYOFMONTH(CURDATE())*0.86,25),2) AS 'Call Average' from ExecutiveMaster INNER JOIN (ActionMaster INNER JOIN (WorkflowMaster INNER JOIN ProjectMaster ON WorkflowMaster.ProjectID = ProjectMaster.ProjectID) ON ActionMaster.ActionID = WorkflowMaster.ActionID) ON ExecutiveMaster.ExecutiveID = WorkflowMaster.ExecutiveID" & _
            " WHERE ActionMaster.ActionDescription = 'Met' AND ExecutiveMaster.ExecutiveID = ?ExecutiveID" & _
            " GROUP BY ExecutiveName, YEAR(WorkflowMaster.MeetingDate), Month" & _
            " ORDER BY ExecutiveName, WorkflowMaster.MeetingDate DESC"

        CountSql = "Select COUNT(*) FROM (Select ExecutiveMaster.ExecutiveName, YEAR (WorkflowMaster.MeetingDate), MONTHNAME(WorkflowMaster.MeetingDate) AS 'Month',COUNT(*) from ExecutiveMaster INNER JOIN (ActionMaster INNER JOIN (WorkflowMaster INNER JOIN ProjectMaster ON WorkflowMaster.ProjectID = ProjectMaster.ProjectID) ON ActionMaster.ActionID = WorkflowMaster.ActionID) ON ExecutiveMaster.ExecutiveID = WorkflowMaster.ExecutiveID" & _
            " WHERE ActionMaster.ActionDescription = 'Met' AND ExecutiveMaster.ExecutiveID = ?ExecutiveID" & _
            " GROUP BY ExecutiveName, YEAR(WorkflowMaster.MeetingDate), Month" & _
            " ORDER BY ExecutiveName, WorkflowMaster.MeetingDate) AS test"

        Params(0) = New GenParameter("?ExecutiveID", dlExecutives.SelectedValue, Data.DbType.UInt16)
        Params(1) = New GenParameter("?Year", Now.Year, Data.DbType.Int16)

        cstmCallAverage.Sql = Sql
        cstmCallAverage.CountSql = CountSql
        cstmCallAverage.Params = Params
        cstmCallAverage.ShowButton = True

    End Sub
End Class
